package com.sxfq.loansupermarket.backsystem.modular.refunds.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.CheckoutMethodInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.PaymentTongJi;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.PaymentTotalInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.SearchPaymentTotalInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.service.IPaymentTotalService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 预约客户信息
 *
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/paymentTotal")
public class PaymentTotalController extends BaseController {

    @Autowired
    IPaymentTotalService service;

    @Autowired
    ICustomerService customerService;

    @Autowired
    private SqlMapper sqlMapper;


    private String PREFIX = "/arrival/customer/";

    /**
     * 跳转到预约客户管理页面
     */
    @RequestMapping(value = "")
    public String recharge(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "/payment_total.html";
    }

    /**
     *
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object rechargeList(SearchPaymentTotalInfo info, HttpServletRequest request) {
        Page<PaymentTotalInfo> page = new PageFactory<PaymentTotalInfo>().defaultPage();
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            List<PaymentTotalInfo> list = new ArrayList<PaymentTotalInfo>();
            list = service.selectPageListInfo(page, info);
            if (list.size() == 0) {
                page.setCurrent(1);
                list = service.selectPageListInfo(page, info);
            }
            page.setRecords(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    @RequestMapping(value = "/getList", method = RequestMethod.POST)
    @ResponseBody
    public List<Map<String, Object>> getList() {
        List<Map<String, Object>> list = service.list();
        return list;
    }

    @RequestMapping(value = "/getReport", method = RequestMethod.POST)
    @ResponseBody
    public List<Map<String, Object>> report(SearchPaymentTotalInfo searchPaymentTotalInfo, String reach) {

        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        String tableName1 = tableHead + "recharge_info";
        String tableName2 = tableHead + "refund_info";
        String sql = " select a.customer_id as customerId, if(b.customer_name is null,'',b.customer_name) as '客户姓名',if(b.customer_phone is null,'',b.customer_phone) as '电话',if(b.customer_sex=1,'男','女') as '性别',if(b.customer_age is null,'',b.customer_age) as '年龄',a.recharge_time as '时间',\n" +
                " a.recharge_people as '收费人', if(b.xcustomer_status is null,'',b.xcustomer_status) as '客户状态',if(b.field_tc is null,'',b.field_tc) as 'TC', a.remarks as  '备注'";
        String selectsql = "select * from checkout_method_info" +
                " where checkout_method in (select checkout_method from "+tableName1+" a where 1=1 " ;
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            selectsql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            selectsql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        selectsql+= ")  group by checkout_method order by num";
        List<CheckoutMethodInfo> list1 = sqlMapper.selectList(selectsql, CheckoutMethodInfo.class);
        if(list1.size()>0){
            sql+=",";
        }
        for (int i = 0; i < list1.size(); i++) {
            if (i < list1.size() - 1) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            } else {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "' ";
            }
        }
        sql += " from " + tableName1 + " a left join " + tableName + " b on a.customer_id=b.id where 1=1 ";

        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or\n" +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%')) ";
        }

        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getXcustomerStatus())) {
            sql += " and b.xcustomer_status = '" + searchPaymentTotalInfo.getXcustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and b.field_tc = '" + searchPaymentTotalInfo.getFieldTc() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " union all\n" +
                " select a.customer_id as customerId, b.customer_name as '客户姓名',b.customer_phone as '电话',(\n" +
                "        CASE b.customer_sex\n" +
                "        WHEN 1 THEN\n" +
                "            '男'\n" +
                "        WHEN 2 THEN\n" +
                "            '女'\n" +
                "        ELSE\n" +
                "            '未知'\n" +
                "        END\n" +
                "    ) as '性别',b.customer_age as '年龄',a.refund_time as '时间',\n" +
                " a.refund_people as '收费人', b.xcustomer_status as '客户状态',b.field_tc as 'TC',  a.remarks as  '备注'";
        if(list1.size()>0){
            sql+=",";
        }
        for (int i = 0; i < list1.size(); i++) {
            if (i < list1.size() - 1) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.refund_money-a.refund_money*2 else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            } else {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.refund_money-a.refund_money*2 else 0 end) as '" + list1.get(i).getCheckoutMethod() + "' ";
            }
        }
        sql += " from " + tableName2 + " a left join " + tableName + " b on a.customer_id=b.id   where 1=1 ";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or\n" +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%')) ";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getXcustomerStatus())) {
            sql += " and b.xcustomer_status = '" + searchPaymentTotalInfo.getXcustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and b.field_tc = '" + searchPaymentTotalInfo.getFieldTc() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.refund_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.refund_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " union all   select '' as customerId, '合计' as '客户姓名','' as '客户电话','' as '性别','' as '年龄','' as  '时间',  " +
                "        '' as '收费人','' as '客户状态','' as 'TC',  a.remarks as  '备注'";
        if(list1.size()>0){
            sql+=",";
        }
        for (int i = 0; i < list1.size(); i++) {
            if (i < list1.size() - 1) {
                sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            } else {
                sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "' ";
            }
        }
        sql += " from " + tableName1 + " a left join " + tableName + " b on a.customer_id=b.id  where 1=1 ";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or\n" +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%')) ";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getXcustomerStatus())) {
            sql += " and b.xcustomer_status = '" + searchPaymentTotalInfo.getXcustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and b.field_tc = '" + searchPaymentTotalInfo.getFieldTc() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getDoctor())) {
            sql += " and b.doctor = '" + searchPaymentTotalInfo.getDoctor() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " union all\n" +
                " select '' as customerId, '合计' as '客户姓名','' as '电话','' as '性别','' as '年龄','' as  '时间',  '' as '收费人','' as '客户状态','' as 'TC', a.remarks as  '备注'";
        if(list1.size()>0){
            sql+=",";
        }
        for (int i = 0; i < list1.size(); i++) {
            if (i < list1.size() - 1) {
                sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.refund_money-a.refund_money*2 else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            } else {
                sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.refund_money-a.refund_money*2 else 0 end) as '" + list1.get(i).getCheckoutMethod() + "' ";
            }
        }
        sql += " from " + tableName2 + " a left join " + tableName + " b on a.customer_id=b.id   where 1=1 ";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or\n" +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%')) ";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getXcustomerStatus())) {
            sql += " and b.xcustomer_status = '" + searchPaymentTotalInfo.getXcustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and b.field_tc = '" + searchPaymentTotalInfo.getFieldTc() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getDoctor())) {
            sql += " and b.doctor = '" + searchPaymentTotalInfo.getDoctor() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.refund_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.refund_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        List<Map<String, Object>> list = sqlMapper.selectList(sql);
        return list;
    }

    @RequestMapping("/sumBorrowAmount")
    @ResponseBody
    public HashMap sumBorrowAmount(@RequestParam(required = false) String customerName,
                                   @RequestParam(required = false) String customerPhone,
                                   @RequestParam(required = false) String checkoutMethod,
                                   @RequestParam(required = false) String fieldTc,
                                   @RequestParam(required = false) String startTime,
                                   @RequestParam(required = false) String endTime) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName1 = tableHead + "recharge_info";
        String sql = "select sum(t1.actual_account_balance) as borrowAmount_sum " +
                " from " + tableName1 + " t1 left join " + this.getTableName() + " t2 on t1.customer_id=t2.id where 1=1";
        if (StringUtil.isNotEmpty(customerName)) {
            sql += " and t2.customer_name = '" + customerName + "'";
        }
        if (StringUtil.isNotEmpty(customerPhone)) {
            sql += " and t2.customer_phone = '" + customerPhone + "'";
        }
        if (StringUtil.isNotEmpty(checkoutMethod)) {
            sql += " and t1.checkout_method = '" + checkoutMethod + "'";
        }
        if (StringUtil.isNotEmpty(fieldTc)) {
            sql += " and t2.field_tc = '" + fieldTc + "'";
        }
        if (StringUtil.isNotEmpty(startTime)) {
            sql += " and t1.recharge_time >= '" + startTime + "'";
        }
        if (StringUtil.isNotEmpty(endTime)) {
            sql += " and t1.recharge_time <= '" + endTime + "'";
        }
        Map<String, Object> result = sqlMapper.selectOne(sql.toString());
        HashMap map = new HashMap();
        if (result != null) {
            map.put("borrowAmount_sum", result.get("borrowAmount_sum"));
        } else {
            map.put("borrowAmount_sum", "0");
        }
        return map;
    }

    @RequestMapping(value = "/paymentExport")
    public void paymentExport(@RequestParam(required = false) String startTime, @RequestParam(required = false) String endTime,
                              @RequestParam(required = false) String fieldTc, @RequestParam(required = false) String xcustomerStatus,
                              HttpServletResponse response) {
        try {
            PaymentTongJi paymentTongJi = new PaymentTongJi();
            paymentTongJi.setStartArrivalTime(startTime);
            paymentTongJi.setEndArrivalTime(endTime);
            paymentTongJi.setFieldTc(fieldTc);
            paymentTongJi.setXcustomerStatus(xcustomerStatus);
            List<PaymentTongJi> list = service.paymentRecordExport(paymentTongJi);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 36; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("年");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("月");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("日");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("姓名");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("病例号");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("单据号");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("TC");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("助理TC");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("主治医生");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("医助/护士");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("转诊人");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("到店途径");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("牙位");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("类别");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("项目");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("折扣/活动价");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("总费用");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("现金");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("扫码支付");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("银行卡");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("医保");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("分期贷款");
            cell.setCellStyle(style);
            cell = row.createCell(22);


            cell.setCellValue("美团");
            cell.setCellStyle(style);
            cell = row.createCell(23);
            cell.setCellValue("华荣时代");
            cell.setCellStyle(style);
            cell = row.createCell(24);
            cell.setCellValue("糯米");
            cell.setCellStyle(style);
            cell = row.createCell(25);
            cell.setCellValue("积分抵扣");
            cell.setCellStyle(style);
            cell = row.createCell(26);
            cell.setCellValue("代金劵");
            cell.setCellStyle(style);
            cell = row.createCell(27);
            cell.setCellValue("VIP收费");
            cell.setCellStyle(style);
            cell = row.createCell(28);
            cell.setCellValue("百度钱包");
            cell.setCellStyle(style);
            cell = row.createCell(29);
            cell.setCellValue("社保卡");
            cell.setCellStyle(style);
            cell = row.createCell(30);
            cell.setCellValue("欠费");
            cell.setCellStyle(style);
            cell = row.createCell(31);
            cell.setCellValue("分期收费");
            cell.setCellStyle(style);
            cell = row.createCell(32);
            cell.setCellValue("正畸编号");
            cell.setCellStyle(style);
            cell = row.createCell(33);
            cell.setCellValue("初诊地");
            cell.setCellStyle(style);
            cell = row.createCell(34);
            cell.setCellValue("电话查询");
            cell.setCellStyle(style);
            cell = row.createCell(35);
            cell.setCellValue("新诊复诊");
            cell.setCellStyle(style);
            cell = row.createCell(36);
            cell.setCellValue("备注");
            cell.setCellStyle(style);

            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                PaymentTongJi map = list.get(i);
                row = sheet.createRow(i + 1);
                String customerName = map.getCustomerName();
                String medicalRecordNumber = map.getMedicalRecordNumber();
                String documentNumber = map.getDocumentNumber();
                String fieldTc1 = map.getFieldTc();
                String TC = map.getTcName();

                String tcAssistant = map.getTcAssistant();
                if (StringUtil.isNotEmpty(map.getTcAssistant())) {
                    tcAssistant = map.getTcAssistant();
                }
                String doctor = map.getDoctor();
                String nurse = "";
                if (StringUtil.isNotEmpty(map.getNurse())) {
                    nurse = map.getNurse();
                }
                String oneChannel = "";
                if (StringUtil.isNotEmpty(map.getOneChannel())) {
                    oneChannel = map.getOneChannel();
                }
                String yawei = "";
                if (StringUtil.isNotEmpty(map.getYawei())) {
                    yawei = map.getYawei();
                }
                String department = "";
                if (StringUtil.isNotEmpty(map.getDepartment())) {
                    department = map.getDepartment();
                }
                String payServiceName = map.getPayServiceName();
                Double actualAccountBalance = 0.0;
                if (map.getActualAccountBalance() != null) {
                    actualAccountBalance = map.getActualAccountBalance();
                }
                Double cash = 0.0;
                if (map.getCash() != null) {
                    cash = map.getCash();
                }
                String sex = map.getCustomerSex();
                String customerPhone = map.getCustomerPhone();
                String xcustomerStatus1 = map.getXcustomerStatus();
                String remarks = map.getRemarks();
                String firstDiagnosis = "";
                String orthodonticNumber = "";
                Double fqsf = 0.00;

                String leftUp = map.getLeftUp();
                String leftDown = map.getLeftDown();
                String rightUp = map.getRightUp();
                String rightDown = map.getRightDown();
                if (leftUp == "" || leftUp == null) {
                    leftUp = "";
                } else {
                    leftUp = "A" + leftUp;
                }
                if (rightUp == "" || rightUp == null) {
                    rightUp = "";
                } else {
                    rightUp = "B" + rightUp;
                }
                if (rightDown == "" || rightDown == null) {
                    rightDown = "";
                } else {
                    rightDown = "C" + rightDown;
                }
                if (leftDown == "" || leftDown == null) {
                    leftDown = "";
                } else {
                    leftDown = "D" + leftDown;
                }
                yawei = leftUp + rightUp + rightDown + leftDown;

                if (map.getFqsf() != null) {
                    fqsf = map.getFqsf();
                }
                Double VIPSF = 0.00;
                if (map.getVIPSF() != null) {
                    VIPSF = map.getVIPSF();
                }
                Double arrears = 0.00;
                if (map.getArrears() != null) {
                    arrears = map.getArrears();
                }
                Double voucher = 0.00;
                if (map.getVoucher() != null) {
                    voucher = map.getVoucher();
                }
                Double integralDeduction = 0.00;
                if (map.getIntegralDeduction() != null) {
                    integralDeduction = map.getIntegralDeduction();
                }
                Double glutinous = 0.00;
                if (map.getGlutinous() != null) {
                    glutinous = map.getGlutinous();
                }
                Double huaRong = 0.00;
                if (map.getHuaRong() != null) {
                    huaRong = map.getHuaRong();
                }
                Double meiTuan = 0.00;
                if (map.getMeiTuan() != null) {
                    meiTuan = map.getMeiTuan();
                }
                Double fqdk = 0.00;
                if (map.getFqdk() != null) {
                    fqdk = map.getFqdk();
                }
                Double medicalInsurance = 0.00;
                if (map.getMedicalInsurance() != null) {
                    medicalInsurance = map.getMedicalInsurance();
                }
                Double scavenging = 0.00;
                if (map.getScavenging() != null) {
                    scavenging = map.getScavenging();
                }
                Double payByCard = 0.00;
                if (map.getPayByCard() != null) {
                    payByCard = map.getPayByCard();
                }
                Double discount = 0.00;
                if (map.getDiscount() != null) {
                    discount = map.getDiscount();
                }
                Double baiduqb = 0.00;
                if (map.getBaiduqb() != null) {
                    baiduqb = map.getBaiduqb();
                }
                Double shebao = 0.00;
                if (map.getShebao() != null) {
                    shebao = map.getShebao();
                }
                String referraler = map.getReferraler();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 36; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(startTime.substring(0, 4));
                row.getCell(1).setCellValue(startTime.substring(5, 7));
                row.getCell(2).setCellValue(startTime.substring(8, 10));
                row.getCell(3).setCellValue(customerName);
                row.getCell(4).setCellValue(medicalRecordNumber);
                row.getCell(5).setCellValue(documentNumber);
                row.getCell(6).setCellValue(fieldTc1); //TC
                row.getCell(7).setCellValue(tcAssistant);//助理TC
                row.getCell(8).setCellValue(doctor);
                row.getCell(9).setCellValue(nurse);

                row.getCell(10).setCellValue(referraler);

                if ("直接上门".equals(oneChannel)) {
                    row.getCell(11).setCellValue("门诊自然到店");
                } else {
                    row.getCell(11).setCellValue(oneChannel);
                }
                row.getCell(12).setCellValue(yawei);

                row.getCell(13).setCellValue(department);


                row.getCell(14).setCellValue(payServiceName);
                row.getCell(15).setCellValue(discount);
                row.getCell(16).setCellValue(actualAccountBalance);
                row.getCell(17).setCellValue(cash);
                row.getCell(18).setCellValue(scavenging);
                row.getCell(19).setCellValue(payByCard);
                row.getCell(20).setCellValue(medicalInsurance);
                row.getCell(21).setCellValue(fqdk);
                row.getCell(22).setCellValue(meiTuan);
                row.getCell(23).setCellValue(huaRong);
                row.getCell(24).setCellValue(glutinous);
                row.getCell(25).setCellValue(integralDeduction);
                row.getCell(26).setCellValue(voucher);
                row.getCell(27).setCellValue(VIPSF);
                row.getCell(28).setCellValue(baiduqb);
                row.getCell(29).setCellValue(shebao);
                row.getCell(30).setCellValue(arrears);
                row.getCell(31).setCellValue(fqsf);
                // row.getCell(25).setCellValue(customerAge);
                row.getCell(32).setCellValue(orthodonticNumber);
                row.getCell(33).setCellValue(firstDiagnosis);
                row.getCell(34).setCellValue(customerPhone);
                row.getCell(35).setCellValue(xcustomerStatus1);
                row.getCell(36).setCellValue(remarks);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getTableName() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return tableName;
    }
}
